﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using WindowsFormsApplication3;


namespace bsy
{
    public partial class Stock : Form
    {
        //连接字符串
        private string connStr = Properties.Settings.Default.bsyConnectionString;

        public Stock()
        {
            InitializeComponent();
        }

        public void Import_Load(object sender, EventArgs e)
        {
            this.WindowState = FormWindowState.Maximized;
            Recent_Stoc_dgv1();
            SeriesCmobobox();
            //ChildSeriesCmobobox();
        }
        //产品系列下拉列表项显示功能
        public void SeriesCmobobox()
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand cmmd = conn.CreateCommand();
                cmmd.CommandText = "select PS_name from productsort where P_id is null";
                SqlDataReader reader = cmmd.ExecuteReader();
                if (reader.HasRows)
                {
                    Stoc_comboBox1.Items.Clear();
                    while (reader.Read())
                    {
                        Stoc_comboBox1.Items.Add(reader[0].ToString());
                    }
                }
                reader.Close();
                conn.Close();
            }
        }
        //产品子系列下拉列表项显示功能
        public void ChildSeriesCmobobox()
        {
            if (Stoc_comboBox1.SelectedItem.ToString() == "")
            {
                return;
            }
            else
            {
                String PS_name = (String)Stoc_comboBox1.SelectedItem;
                String sql1 = string.Format("select PS_id from productsort where PS_name='{0}'", PS_name);
                DataSet ds = new DataSet();
                ds = CommonDB.Query(sql1);
                int PS_id = (int)ds.Tables[0].Rows[0][0];

                String sql2 = "select count(*) from productsort where P_id='" + PS_id + "'";
                DataSet dst = new DataSet();
                dst = CommonDB.Query(sql2);
                int count = (int)dst.Tables[0].Rows[0][0];
                if (count == 0)
                {
                    Stoc_comboBox2.Text = "";
                    Stoc_comboBox2.Items.Clear();
                }
                else
                {
                    using (SqlConnection conn = new SqlConnection(connStr))
                    {


                        conn.Open();
                        SqlCommand cmmd = conn.CreateCommand();
                        cmmd.CommandText = "select PS_name from productsort where P_id='" + PS_id + "'";
                        SqlDataReader reader = cmmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            Stoc_comboBox2.Items.Clear();
                            while (reader.Read())
                            {
                                Stoc_comboBox2.Items.Add(reader[0].ToString());
                            }
                        }
                        reader.Close();
                        conn.Close();

                    }
                }
            }
        }
 
        //显示近一个月内的进货记录
        public void Recent_Stoc_dgv1()
        {
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            string sql = "select stock_id,p2.PS_name 产品系列,p1.PS_name 产品子系列,number 货号,S_name 产品名称,guige 容量,price 零售价,count 数量,stocktime 进货日期,remark 备注 from stock,productsort p1,productsort p2 where p1.P_id=p2.PS_id and stock.PS_id=p1.PS_id AND (stocktime between ((SELECT GETDATE())-30) and (SELECT GETDATE())) order by stocktime";
            SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            this.Stoc_dgv1.DataSource = ds.Tables[0].DefaultView;
            this.Stoc_dgv1.Columns[0].Visible = false;
            ds.Dispose();
            conn.Close();
        }
        //显示全部历史进货记录
        //public void All_Stoc_dgv1()
        //{
        //    SqlConnection conn = new SqlConnection(connStr);
        //    conn.Open();
        //    string sql = "select stock_id,p2.PS_name 产品系列,p1.PS_name 产品子系列,number 货号,S_name 产品名称,guige 容量,price 零售价,count 数量,stocktime 订货日期,remark 备注 from stock,productsort p1,productsort p2 where p1.P_id=p2.PS_id and stock.PS_id=p1.PS_id order by stocktime";
        //    SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
        //    DataSet ds = new DataSet();
        //    sda.Fill(ds);
        //    this.Stoc_dgv1.DataSource = ds.Tables[0].DefaultView;
        //    this.Stoc_dgv1.Columns[0].Visible = false;
        //    ds.Dispose();
        //    conn.Close();
        //}


        public void Stoc_SaveButton_Click(object sender, EventArgs e)
        {
            String number = Stoc_textBox1.Text.Trim();
            String stocktime = dateTimePicker1.Value.ToShortDateString();
            String remark = Stoc_textBox7.Text.Trim();
            String S_name = Stoc_textBox3.Text.Trim();
            String guige = Stoc_textBox4.Text.Trim();
            String SeriesName = (String)Stoc_comboBox1.SelectedItem;
            String ChildSeriesName = (String)Stoc_comboBox2.SelectedItem;

            Boolean flag = number == "" || S_name == "" || guige == "" || Stoc_textBox5.Text == "" || Stoc_textBox6.Text == "";
            if (flag == true)
            {
                MessageBox.Show("请检查是否输入完整数据！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            if (Stoc_comboBox1.SelectedIndex < 0)
            {
                MessageBox.Show("请选择产品系列！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            if (Stoc_comboBox2.SelectedIndex < 0)
            {
                MessageBox.Show("请选择产品子系列！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            try
            {
                int count = Int32.Parse(Stoc_textBox5.Text.Trim());
                float price = float.Parse(Stoc_textBox6.Text.Trim());

                //从产品类型表中取出产品系列的ID号，目的是为了将取出的产品系列的ID号插进stock表的PS_id列
                String sql1 = "select PS_id from productsort where PS_name='" + ChildSeriesName + "'";
                DataSet ds = new DataSet();
                ds = CommonDB.Query(sql1);
                int PS_id = (int)ds.Tables[0].Rows[0][0];

                //将进货信息存进数据库
                String sql2 = "insert into stock values('" + PS_id + "','" + number + "','" + S_name + "','" + guige + "','" + count + "','" + price + "','" + stocktime + "','" + remark + "')";
                int temp1 = CommonDB.ExecuteSql(sql2);
                MessageBox.Show("保存成功！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

                 //将录入数据同步增加到库存表之中
                try
                {
                    //判断数据库之中书否存在该产品，由货号来查询
                    String sql3 = "select count(*) from storeroom where number='"+number+"'"; 
                    DataSet dset = new DataSet();
                    dset = CommonDB.Query(sql3);
                    int temp2 = (int)dset.Tables[0].Rows[0][0];
                    //temp=0表示库存表之中不存在该产品，则将该产品进货时录入的数据全部同步增加到库存表之中
                    if (temp2 == 0) 
                    {
                        String sql4 = "insert into storeroom values('"+number+"','"+PS_id+"','"+S_name+"','"+count+"','"+guige+"','"+price+"','"+remark+"')";
                        CommonDB.ExecuteSql(sql4);
                    }
                    //如果库存表之中存在该产品，则将新录入产品的进货数量与原来库存表之中该产品剩余数量进行相加
                    else 
                    {
                        //从库存中取出已经录入库存表的产品的数量值,然后与新进货的同种产品数量相加
                        String sql5 = "select count from storeroom where number='"+number+"'";
                        DataSet dst = new DataSet();
                        dst = CommonDB.Query(sql5);
                        int storeroom_count = (int)dst.Tables[0].Rows[0][0];
                        int new_storeroom_count = count + storeroom_count;
                        String sql6 = "update storeroom set count='" + new_storeroom_count + "' where number='" + number + "'";
                        CommonDB.ExecuteSql(sql6);
                        dst.Dispose();
                    }
                    dset.Dispose();
                }
                catch(Exception se)
                {
                    MessageBox.Show("错误提示："+se.Message);
                }
 

                //保存成功后将文本框置为空
                if (temp1 == 1)
                {
                    Stoc_textBox1.Text = "";
                    Stoc_textBox7.Text = "";
                    Stoc_textBox3.Text = "";
                    Stoc_textBox4.Text = "";
                    Stoc_textBox5.Text = "";
                    Stoc_textBox6.Text = "";
                    Stoc_comboBox1.Text = "";
                    Stoc_comboBox2.Text = "";
                }
                Import_Load(sender, e);
                ds.Dispose();
            }
            catch(FormatException fe)
            {
                MessageBox.Show("请检查进货数量或者零售价是否输入正确！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            catch(SqlException sqle)
            {
                MessageBox.Show("请仔细检查输入是否正确！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }

           
        }

        private void Stoc_ShowAllStockButton_Click(object sender, EventArgs e)
        {
            String stocktime1 = Stoc_dateTimePicker2.Value.ToShortDateString();
            String stocktime2 = Stoc_dateTimePicker3.Value.ToShortDateString();
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            String sql = "select stock_id,p2.PS_name 产品系列,p1.PS_name 产品子系列,number 货号,S_name 产品名称,guige 容量,price 零售价,count 数量,stocktime 订货日期,remark 备注 from stock,productsort p1,productsort p2 where p1.P_id=p2.PS_id and stock.PS_id=p1.PS_id and (stocktime between '"+stocktime1+"' and '"+stocktime2+"') order by stocktime";
            SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            this.Stoc_dgv1.DataSource = ds.Tables[0].DefaultView;
            this.Stoc_dgv1.Columns[0].Visible = false;
            ds.Dispose();
            conn.Close();
        }

        private void RefreshToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Import_Load(sender, e);
        }

        private void Stoc_AddButton1_Click(object sender, EventArgs e)
        {
            SeriesAdd sad = new SeriesAdd();
            sad.ShowDialog();
            if (sad.DialogResult == DialogResult.OK)
            {
                SeriesCmobobox();
                sad.Close();
            }

        }

        private void Stoc_AddButton2_Click(object sender, EventArgs e)
        {
            if (Stoc_comboBox1.Text == "")
            {
                MessageBox.Show("请选择产品系列！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            else
            {
                ChildSeriesAdd csad = null;
                if (Stoc_comboBox1.Text != string.Empty)
                {
                    csad = new ChildSeriesAdd(Stoc_comboBox1.Text.ToString());
                    csad.ShowDialog();
                }
                if (csad.DialogResult == DialogResult.OK)
                {
                    ChildSeriesCmobobox();
                    csad.Close();
                }
            }
            
        }

        private void Stoc_DeleteButton_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("确定删除选定行数据？", "系统提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.OK)
            {
                //删除进货表之中的数据库数据
                string sqlStr = "delete from stock where stock_id='" + Stoc_dgv1.CurrentRow.Cells["stock_id"].FormattedValue.ToString() + "' ";
                
                //在库存表之中的数据库数据的数量要减去所删除那行数据的数量值
                int delcount = Int32.Parse(Stoc_dgv1.CurrentRow.Cells[7].FormattedValue.ToString());//取出该行数量列的值，7为数量列的索引
                String delnum = Stoc_dgv1.CurrentRow.Cells[3].FormattedValue.ToString();//取出该行货号列的值，3为货号列的索引
                //从库存表中取出该产品的库存数量
                String sql1 = "select count from storeroom where number='"+delnum+"'";
                DataSet dst = new DataSet();
                dst = CommonDB.Query(sql1);
                int storeroom_count = (int)dst.Tables[0].Rows[0][0];

                //从库存表中进行数量相减操作
                int new_storeroom_count = storeroom_count - delcount;
                String update_sql = "update storeroom set count='" + new_storeroom_count + "' where number='" + delnum + "'";
                CommonDB.ExecuteSql(update_sql);//先减去库存产品数量
               
                CommonDB.ExecuteSql(sqlStr);//后对进货表中的数据进行删除
                Import_Load(sender, e);
                dst.Dispose();
            }
        }

        private void Stoc_AlterButton1_Click(object sender, EventArgs e)
        {
            if (Stoc_comboBox1.Text == "")
            {
                MessageBox.Show("请选择需要修改的产品系列！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else
            {
                String sn = Stoc_comboBox1.Text;
                String sql = "update productsort set PS_name='" + sn + "' where PS_name='" + S_Name + "'";
                int temp = CommonDB.ExecuteSql(sql);
                if (temp == 1)
                {
                    MessageBox.Show("修改成功！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    SeriesCmobobox();
                }
            }
        }

        private void Stoc_AlterButton2_Click(object sender, EventArgs e)
        {
            if (Stoc_comboBox2.Text == "")
            {
                MessageBox.Show("请选择需要修改的产品子系列！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else
            {
                String scn = Stoc_comboBox2.Text.Trim();
                String sql = "update productsort set PS_name='" + scn + "' where PS_name='" + SChild_Name + "'";
                int temp = CommonDB.ExecuteSql(sql);
                if (temp == 1)
                {
                    MessageBox.Show("修改成功！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    ChildSeriesCmobobox();
                }
            }
        }


        public string S_Name = "";
        public void Stoc_comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            S_Name = (String)Stoc_comboBox1.SelectedItem;
            ChildSeriesCmobobox();
        }

        private string SChild_Name = "";
        private void Stoc_comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            SChild_Name = (String)Stoc_comboBox2.SelectedItem;
        }
    }
}
